"""Search_omega instances"""

from mydb import mysql
from objects import articles

articles=articles([mysql, None, None, None,['import_so_map_articles','art_id','unvcode']], 
		
		[
		['art_id','integer','NOT NULL','Search.articles'],
		['section_id','integer','NOT NULL','Search.ssections'],
		['sectname','character varying(70)','NOT NULL','Search.ssections'],
		['doc_type','integer','NOT NULL','Search.doclist'],
		['doc_name','character varying(100)','NOT NULL','Search.doctypes'],
		['designatio','character varying(80)','','Search.articles'],
		['name','character varying(250)','','Search.articles'],
		['note', 'character varying(252)','', 'Search.articles'],
		['mu_id','integer','NOT NULL','Search.articles'],
		['purchased','character(1)','','Search.articles'],
		['format','character varying(200)','','Search.doclist'],
		['modifdate','timestamp','','Search.articles'],
		['version_id','integer','','Search.doclist']], 
		
		[
		['UNVCODE','integer','NOT NULL','Omega.konstrobj'],
		['REVISION','integer','NOT NULL','Omega.konstrobj'],
		['ITEMTYPE','integer','NOT NULL','Omega.konstrobj'],
		['ITEMTYPE_NAME','character varying(40)','NOT NULL','Omega.ko_types.name'],
		['SIGN','character varying(100)','NOT NULL','Omega.konstrobj'],
		['NAME','character varying(200)','','Omega.konstrobj'],
		['NOTICE','character varying(100)','','Omega.konstrobj'],
		['MEASCODE','integer','NOT NULL','Omega.konstrobj'],
		['MEASNAME','character varying(40)','NOT NULL','Omega.measures.name'],
		['SUPPLYTYPE','integer','NOT NULL','Omega.konstrobj'],
		['FORMAT','character varying(15)','','Omega.konstrobj'],
		['OWNER','integer','NOT NULL','Omega.konstrobj'],
		['OWNER_NAME','character varying(100)','NOT NULL','Omega.owner_name'],
		['RECDATE','timestamp','NOT NULL','Omega.konstrobj'],
		['PRODCODE','integer','NOT NULL','Omega.konstrobj'],
		['OMP_OBJECTS_CODE','integer','NOT NULL','Omega.omp_objects.code'],
		['FIRST_PROMCODE','integer','NOT NULL','Omega.businessobj_promotion.code'],
		['LAST_PROMCODE','integer','NOT NULL','Omega.businessobj_promotion.code'],
		['CURRENT_STATE','character varying(40)','','Omega.businessobj_states.name'],
		['STATEDATE','timestamp','NOT NULL','Omega.businessobj_promotion.statedate'],
		['LASTNAME','character varying(40)','NOT NULL','Omega.businessobj_promotion.lastname']], 'articles', 'konstrobj', 
		'''select 

DISTINCT 

a.art_id, a.section_id, ss.sectname, d.doc_type, e.doc_name, a.designatio, a.name, a.note, a.mu_id, a.purchased, d.format, a.modifdate, d.version_id

     FROM articles a, articles_view av, archives ar, pc, ssections ss, doclist d, doctypes e

          WHERE
               
               e.doc_type=d.doc_type
      
               AND d.doc_id=a.doc_id
               
               AND a.section_id=ss.section_id

               AND extract(YEAR from a.chkindate)>2005

               AND a.art_id>0

               AND ar.archive_id=av.archive_id

               AND (ar.archive_id=31
                        OR ar.archive_id=34
                        OR ar.archive_id=35
                        OR ar.archive_id=36
                        OR ar.archive_id=37
                        OR ar.archive_id=38
                        OR ar.archive_id=41
                        OR ar.archive_id=42
                        OR ar.archive_id=43)

               AND CHAR_LENGTH(TRIM(a.name))>0

               AND 

                (
                 (a.art_id=pc.part_aid
                  OR a.art_id=pc.proj_aid)

                 AND av.art_id=pc.proj_aid
                 AND CHAR_LENGTH(TRIM(a.designatio))>0
                 AND (a.SECTION_ID=1
                          OR a.SECTION_ID=3
                          OR a.SECTION_ID=4
                          OR a.SECTION_ID=8)
                )

UNION ALL

select 

DISTINCT 

a.art_id, a.section_id, ss.sectname, d.doc_type, e.doc_name, a.designatio, a.name, a.note, a.mu_id, a.purchased, d.format, a.modifdate, d.version_id

     FROM 

articles a, articles_view av, archives ar, pc, ssections ss, doclist d, doctypes e

          WHERE
               
               e.doc_type=d.doc_type
      
               AND d.doc_id=a.doc_id

               AND a.section_id=ss.section_id

               AND extract(YEAR from a.chkindate)>2005

               AND a.art_id>0

               AND ar.archive_id=av.archive_id

               AND (ar.archive_id=31
                        OR ar.archive_id=34
                        OR ar.archive_id=35
                        OR ar.archive_id=36
                        OR ar.archive_id=37
                        OR ar.archive_id=38
                        OR ar.archive_id=41
                        OR ar.archive_id=42
                        OR ar.archive_id=43)

               AND CHAR_LENGTH(TRIM(a.name))>0

               AND 
                
                (
                 a.art_id=pc.part_aid
                 AND av.art_id=pc.proj_aid
                 AND CHAR_LENGTH(TRIM(a.designatio))=0
                 AND (a.SECTION_ID=5
                          OR a.SECTION_ID=6
                          OR a.SECTION_ID=7)
                )''', 


				'''SELECT a.UNVCODE, a.REVISION, a.ITEMTYPE, b."NAME" AS "ITEMTYPE_NAME", a.SIGN,
a."NAME", a.NOTICE, a.MEASCODE, c."NAME" as MEASNAME, a.SUPPLYTYPE, a.FORMAT, 
a.OWNER, d."NAME" as "OWNER_NAME", a.RECDATE, a.PRODCODE, e.CODE as "OMP_OBJECTS_CODE",
g.CODE as "FIRST_PROMCODE", h.CODE as "LAST_PROMCODE", i.CURRENT_STATE, i.STATEDATE, i.LASTNAME


FROM OMP_ADM.KONSTROBJ a, OMP_ADM.KO_TYPES b, 
    OMP_ADM.MEASURES c, OMP_ADM.OWNER_NAME d,
    OMP_ADM.BUSINESS_OBJECTS e,
    
    (SELECT a.DOCCODE as "UNVCODE", MIN(b.CODE) AS "CODE"
        FROM OMP_ADM.BUSINESS_OBJECTS a, OMP_ADM.BUSINESSOBJ_PROMOTION b, OMP_ADM.BUSINESSOBJ_STATES c
            WHERE b.BUSINESSOBJ=a.CODE
                AND c.CODE=b.CURRENT_STATE
                AND a."TYPE" IN (0,1,2,3,4,5,6,22)
        GROUP BY a.DOCCODE) g,

    (SELECT a.DOCCODE as "UNVCODE", MAX(b.CODE) AS "CODE"
        FROM OMP_ADM.BUSINESS_OBJECTS a, OMP_ADM.BUSINESSOBJ_PROMOTION b, OMP_ADM.BUSINESSOBJ_STATES c
            WHERE b.BUSINESSOBJ=a.CODE
                AND c.CODE=b.CURRENT_STATE
                AND a."TYPE" IN (0,1,2,3,4,5,6,22)
        GROUP BY a.DOCCODE) h,

    (SELECT a.CODE, b."NAME" as "CURRENT_STATE", a.STATEDATE, a.LASTNAME 
        FROM OMP_ADM.BUSINESSOBJ_PROMOTION a, OMP_ADM.BUSINESSOBJ_STATES b
            WHERE b.CODE=a.CURRENT_STATE) i

            
            WHERE b.CODE=a.ITEMTYPE
                AND c.CODE=a.MEASCODE
                AND d.OWNER=a.OWNER
                AND e.DOCCODE=a.UNVCODE
                AND e."TYPE"=a.ITEMTYPE
                AND g.UNVCODE=a.UNVCODE
                AND h.UNVCODE=a.UNVCODE
                AND h.CODE=i.CODE
                AND a.ITEMTYPE IN (0,1,2,3,4,5,6,22)''')



ssections=0